package com.apes.fico.finance.respository;

import com.apes.fico.finance.model.gl.ChartAmount;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface ChartAmoutRepository extends CustomRepository<ChartAmount, String> {
    ChartAmount findByCompany_IdAndPeriodAndAccountingOperatingChart_Id(String company, int period, String accId);

    @Query("select new map(T.id as id,T.credit as credit,T.debit as debit," +
            "T.openingBalance as openingBalance," +
            "(T.openingBalance+(T.debit-T.credit)) as endingBalance," +
            "T.accountingOperatingChart.id as acc_id,T.accountingOperatingChart.code as code)" +
            " from ChartAmount T where 1=1 and (T.company.id=:company and T.period=:period and T.year=:year) order by T.id")
    List<Map<String, String>> findAllByCompanyAndPeriodAndYear(@Param("company") String company, @Param("period") int period, @Param("year") int year);

    @Query("select new map(T.id as id,T.accountingOperatingChart.code as code ,T.accountingOperatingChart.balanorient as balanorient," +
            "T.accountingOperatingChart.name as name," +
            "T.company.id as company," +
            "               T.year as year,T.period as period," +
            "                 T.credit as credit,T.debit as debit,T.openingBalance as openingBalance" +
            "                 ) " +
            "   from ChartAmount T" +
            "       where 1=1 and (T.company.id=:company )" +
            "            and (T.period between :period and :stopPeriod) " +
            "               and (T.year between  :year and :stopYear)" +
            "                 and (T.accountingOperatingChart.classes between :classes and :stopClasses) ")
    List<Map<String, Object>> findByBody(@Param("company") String company,
                                         @Param("period") int period, @Param("stopPeriod") int stopPeriod,
                                         @Param("year") int year, @Param("stopYear") int stopYear, @Param("classes") double classes, @Param("stopClasses") double stopClasses);

    @Query("select new map(T.id as id,T.accountingOperatingChart.code as code ,T.accountingOperatingChart.balanorient as balanorient," +
            "T.accountingOperatingChart.name as name," +
            "T.company.id as company," +
            "               T.year as year,T.period as period," +
            "                 T.credit as credit,T.debit as debit,T.openingBalance as openingBalance" +
            "                 ) " +
            "   from ChartAmount T" +
            "       where 1=1 and (T.company.id=:company and T.accountingOperatingChart.code =:accCode)" +
            "            and (T.period between :period and :stopPeriod) " +
            "               and (T.year between  :year and :stopYear)" +
            "                  ")
    List<Map<String, Object>> findByBodyAndAccCode(@Param("company") String company, @Param("accCode") String accCode,
                                                   @Param("period") int period, @Param("stopPeriod") int stopPeriod,
                                                   @Param("year") int year, @Param("stopYear") int stopYear);

    @Query(value = "SELECT new map (t.id as id ,t.company.id as company_id ,t.period as period,0 as type) FROM ChartAmount t where t.period = :period and t.company.id = :company order by t.id")
    Page<Map> findAllIdByPeriodAndCompany_Id(int period, String company, Pageable pageable);

    //批量更新期初余额为0
    @Modifying
    @Query(value = "update ChartAmount t set t.openingBalance = 0 where t.period = :period and t.company.id = :company")
    void updateOpenBalanceByPeriodAndCompany_Id(int period, String company);
}
